package org.edu.common.utils;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.edu.common.dto.ExcelPo;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;

/**
 * @author Erik Zhang
 * @version 1.0
 * @since 2021/2/21
 */
public class PoiUtils {

    public static JSONObject JSON= new JSONObject();

    static {
        String jsonStr = "{\"qstatus\":[{\"value\":\"启用\",\"id\":1},{\"value\":\"禁用\",\"id\":0}],\"status\":[{\"value\":\"可用\",\"id\":1},{\"value\":\"禁用\",\"id\":0}],\"statusFb\":[{\"value\":\"发布\",\"id\":1},{\"value\":\"未发布\",\"id\":0}],\"shifou\":[{\"value\":\"是\",\"id\":1},{\"value\":\"否\",\"id\":0}],\"duicuo\":[{\"value\":\"√\",\"id\":1},{\"value\":\"×\",\"id\":0}],\"cgxz\":[{\"value\":\"科研成果\",\"id\":\"ky\"},{\"value\":\"项目成果\",\"id\":\"xm\"}],\"report\":[{\"value\":\"论文\",\"id\":1},{\"value\":\"科研课题\",\"id\":2},{\"value\":\"其他\",\"id\":3}],\"sex\":[{\"value\":\"男\",\"id\":1},{\"value\":\"女\",\"id\":2}],\"sexStr\":[{\"value\":\"男\",\"id\":\"1\"},{\"value\":\"女\",\"id\":\"2\"}],\"userType\":[{\"value\":\"校内人员\",\"id\":1},{\"value\":\"校外人员\",\"id\":2}],\"projectType\":[{\"value\":\"未完成\",\"id\":1},{\"value\":\"已完成\",\"id\":2}],\"projectAudit\":[{\"value\":\"未审核\",\"id\":1},{\"value\":\"已审核\",\"id\":2},{\"value\":\"审核不通过\",\"id\":3}],\"fundAudit\":[{\"value\":\"未审核\",\"id\":0},{\"value\":\"已通过\",\"id\":1},{\"value\":\"不通过\",\"id\":2}],\"meetKaoqing\":[{\"value\":\"未考勤\",\"id\":0},{\"value\":\"正常\",\"id\":1},{\"value\":\"迟到\",\"id\":2},{\"value\":\"未到\",\"id\":3}],\"meetAudit\":[{\"value\":\"未审核\",\"id\":0},{\"value\":\"已通过\",\"id\":1},{\"value\":\"不通过\",\"id\":2}],\"zhichen\":[{\"value\":\"助教\",\"id\":\"1\"},{\"value\":\"讲师\",\"id\":\"2\"},{\"value\":\"高级讲师\",\"id\":\"3\"},{\"value\":\"三级讲师\",\"id\":\"4\"}],\"xueli\":[{\"value\":\"专科\",\"id\":\"1\"},{\"value\":\"本科\",\"id\":\"2\"},{\"value\":\"硕士研究生\",\"id\":\"3\"},{\"value\":\"博士研究生\",\"id\":\"4\"}],\"hqUserType\":[{\"value\":\"医疗人员\",\"id\":\"1\"},{\"value\":\"保安员\",\"id\":\"3\"}],\"hqUserStatus\":[{\"value\":\"在岗\",\"id\":1},{\"value\":\"离岗\",\"id\":2}],\"anbaozStatus\":[{\"value\":\"正常\",\"id\":1},{\"value\":\"不正常\",\"id\":0}],\"ctStatus\":[{\"value\":\"开放\",\"id\":1},{\"value\":\"未开放\",\"id\":0}],\"wxStatus\":[{\"value\":\"完成\",\"id\":1},{\"value\":\"在修\",\"id\":2},{\"value\":\"未修\",\"id\":0}],\"mainTain\":[{\"value\":\"修缮说明\",\"id\":6},{\"value\":\"财政拨款\",\"id\":5},{\"value\":\"贷款\",\"id\":4},{\"value\":\"自筹资金\",\"id\":3},{\"value\":\"捐助\",\"id\":2},{\"value\":\"集资\",\"id\":1},{\"value\":\"其它\",\"id\":0}],\"zcStatus\":[{\"value\":\"闲置\",\"id\":\"unallocated\"},{\"value\":\"被借领\",\"id\":\"apply\"},{\"value\":\"维修中\",\"id\":\"maintain\"},{\"value\":\"报废\",\"id\":\"retire\"}],\"applyFor\":[{\"value\":\"借用\",\"id\":\"1\"},{\"value\":\"领用\",\"id\":\"2\"}],\"zcWxStatus\":[{\"value\":\"维修完成\",\"id\":1},{\"value\":\"维修中\",\"id\":2}],\"revertStatus\":[{\"value\":\"未归还\",\"id\":0},{\"value\":\"已归还\",\"id\":1}],\"bxStatus\":[{\"value\":\"驳回\",\"id\":0},{\"value\":\"待审批\",\"id\":1},{\"value\":\"待维修\",\"id\":2},{\"value\":\"已维修\",\"id\":3},{\"value\":\"未维修\",\"id\":4}],\"mzm\":[{\"value\":\"汉族\",\"id\":\"1\"},{\"value\":\"藏族\",\"id\":\"2\"},{\"value\":\"蒙古族\",\"id\":\"3\"},{\"value\":\"维吾尔族\",\"id\":\"4\"},{\"value\":\"回族\",\"id\":\"5\"},{\"value\":\"壮族\",\"id\":\"6\"},{\"value\":\"满族\",\"id\":\"7\"}],\"zzmmm\":[{\"value\":\"群众\",\"id\":\"1\"},{\"value\":\"团队\",\"id\":\"2\"},{\"value\":\"党员\",\"id\":\"3\"},{\"value\":\"预备党员\",\"id\":\"4\"}],\"jdfs\":[{\"value\":\"住校\",\"id\":1},{\"value\":\"走读\",\"id\":2}],\"bmfsm\":[{\"value\":\"在线报名\",\"id\":1},{\"value\":\"线下报名\",\"id\":2}],\"xz\":[{\"value\":\"两年\",\"id\":3},{\"value\":\"三年\",\"id\":4},{\"value\":\"四年\",\"id\":5},{\"value\":\"五年\",\"id\":6},{\"value\":\"八年\",\"id\":7}],\"jkzkm\":[{\"value\":\"健康或良好\",\"id\":\"1\"},{\"value\":\"一般或较弱\",\"id\":\"2\"},{\"value\":\"有慢性病\",\"id\":\"3\"},{\"value\":\"残疾\",\"id\":\"4\"}],\"hklbm\":[{\"value\":\"城市\",\"id\":\"1\"},{\"value\":\"城镇\",\"id\":\"2\"},{\"value\":\"农村\",\"id\":\"3\"}],\"sfsldrk\":[{\"value\":\"非流动人口\",\"id\":\"0\"},{\"value\":\"流动人口\",\"id\":\"1\"}],\"stuStatye\":[{\"value\":\"新生\",\"id\":\"XS\"},{\"value\":\"在校\",\"id\":\"ZX\"},{\"value\":\"休学\",\"id\":\"XX\"},{\"value\":\"退学\",\"id\":\"TX\"},{\"value\":\"开除\",\"id\":\"KC\"},{\"value\":\"毕业\",\"id\":\"BY\"},{\"value\":\"肄业\",\"id\":\"YY\"},{\"value\":\"转学\",\"id\":\"ZXX\"},{\"value\":\"结业\",\"id\":\"JY\"}],\"ydlbm\":[{\"value\":\"退学\",\"id\":\"1\"},{\"value\":\"休学\",\"id\":\"2\"},{\"value\":\"转专业\",\"id\":\"3\"},{\"value\":\"留级\",\"id\":\"4\"},{\"value\":\"转出\",\"id\":\"6\"}],\"gxm\":[{\"value\":\"父子\",\"id\":\"1\"},{\"value\":\"母子\",\"id\":\"2\"},{\"value\":\"父女\",\"id\":\"3\"},{\"value\":\"母女\",\"id\":\"4\"},{\"value\":\"兄弟\",\"id\":\"5\"},{\"value\":\"姐妹\",\"id\":\"6\"}],\"xjAuditStatus\":[{\"value\":\"待审核\",\"id\":0},{\"value\":\"审核通过\",\"id\":1},{\"value\":\"审核不通过\",\"id\":2}],\"auditStatus\":[{\"value\":\"通过\",\"id\":1},{\"value\":\"不通过\",\"id\":2}],\"levelType\":[{\"value\":\"事假\",\"id\":1},{\"value\":\"病假\",\"id\":2},{\"value\":\"丧假\",\"id\":3}],\"jlJb\":[{\"value\":\"国家级奖励\",\"id\":\"GJJJL\"},{\"value\":\"省级奖励\",\"id\":\"SJJL\"},{\"value\":\"市级奖励\",\"id\":\"SJJL\"},{\"value\":\"区级奖励\",\"id\":\"QJJL\"},{\"value\":\"校级奖励\",\"id\":\"XJJL\"}],\"jllb\":[{\"value\":\"个人\",\"id\":1},{\"value\":\"集体\",\"id\":2}],\"courseName\":[{\"value\":\"早自习\",\"id\":\"早自习\"},{\"value\":\"晚自习\",\"id\":\"晚自习\"}]}";
        JSON = new JSONObject(jsonStr);
    }

    public static String getValue(String code,String value) {
        JSONArray jsonArray = JSON.getJSONArray(code);
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            if(value.equals(jsonObject.get("id")+"")){
                return jsonObject.getStr("value");
            }
        }
        return value == null ? "":value;
    }

  //  importExcel(List<Map<String, Object>> mapList,)

    public static File createUserExcelFile(List<?> records ,List<ExcelPo> excelVos,boolean isXh,String tempMsg) {
        if (records == null) {
            records = new ArrayList<>();
        }
        Workbook workbook = new XSSFWorkbook();
        //创建一个sheet，括号里可以输入sheet名称，默认为sheet0
        Sheet sheet = workbook.createSheet();
        int intx = 0;
        Row row0 = sheet.createRow(intx);
        if(StrUtil.isNotEmpty(tempMsg)){
            row0.createCell(0).setCellValue(tempMsg);
            row0.setHeight((short) 600);
            CellRangeAddress region = new CellRangeAddress( 0, 0,0, excelVos.size()-1);
            sheet.addMergedRegion(region);

            intx++;
            row0 = sheet.createRow(intx);
        }


        int columnIndex = 0;
        if(isXh){
            row0.createCell(columnIndex).setCellValue("序号");
        }else{
            columnIndex = -1;
        }
        for (ExcelPo excelPo: excelVos) {
            columnIndex++;
            row0.createCell(columnIndex).setCellValue(excelPo.getName());
        }
        for (int i = 0; i < records.size(); i++) {
            Map<String, Object> map = new HashMap<String, Object>();
            BeanUtil.copyProperties( records.get(i), map);
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < columnIndex + 1; j++) {
                row.createCell(j);
            }
            columnIndex = 0;
            row.getCell(columnIndex).setCellValue(i + 1);//序号的
            for (ExcelPo excelPo: excelVos) {
                columnIndex++;
                Object obj = map.get(excelPo.getKey());
                if(excelPo.getRender()!=null){
                    row.createCell(columnIndex).setCellValue(excelPo.getRender().render( obj ));
                }else if(excelPo.getCode()!=null){
                    row.createCell(columnIndex).setCellValue(getValue(excelPo.getCode(), obj +""));
                }else{
                    if(obj !=null && obj instanceof Date){
                        String str = DateUtil.format((Date) obj,"yyyy-MM-dd HH:mm:ss");
                        row.createCell(columnIndex).setCellValue(str);
                    }else{
                        row.createCell(columnIndex).setCellValue(obj ==null?"": obj +"");
                    }
                }
            }
        }
        //调用PoiUtils工具包
        return PoiUtils.createExcelFile(workbook, "download_user");
    }


    public static void downloadFile(HttpServletResponse response,List<?> data ,List<ExcelPo> excelVos) {
        downloadFileOld(response,data,excelVos,true,"");
    }
    public static void downloadFile(HttpServletResponse response,List<?> data ,List<ExcelPo> excelVos,String tempMsg) {
        downloadFileOld(response,data,excelVos,false,tempMsg);
    }
    public static void downloadTempFile(HttpServletResponse response,List<?> data ,List<ExcelPo> excelVos) {
        downloadFileOld(response,data,excelVos,false,"");
    }
    /**
     * 下载文件
     * @param response
     */
    public static void downloadFileOld(HttpServletResponse response,List<?> data ,List<ExcelPo> excelVos,boolean xh,
                                       String tempMsg) {
        File file = createUserExcelFile(data, excelVos,xh,tempMsg);
        System.out.println(file.getPath());
        String newFileName = file.getName();
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + new String(newFileName.getBytes("ISO-8859-1"), "UTF-8"));
            BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());
            InputStream is = new FileInputStream(file.getAbsolutePath());
            BufferedInputStream bis = new BufferedInputStream(is);
            int length = 0;
            byte[] temp = new byte[1 * 1024 * 10];
            while ((length = bis.read(temp)) != -1) {
                bos.write(temp, 0, length);
            }
            bos.flush();
            bis.close();
            bos.close();
            is.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 关闭对象，连接
     * @param closeable
     */
    public static void closeQuietly(final Closeable closeable) {
        try {
            if (closeable != null) {
                closeable.close();
            }
        } catch (final IOException ioe) {
            // ignore
        }
    }

    /**
     * 生成Excel文件
     * @param workbook
     * @param fileName
     * @return
     */
    public static File createExcelFile(Workbook workbook, String fileName) {
        OutputStream stream = null;
        File file = null;
        try {            //用了createTempFile，这是创建临时文件，系统会自动给你的临时文件编号，所以后面有号码，你用createNewFile的话就完全按照你指定的名称来了
            file = File.createTempFile(fileName, ".xlsx");
            stream = new FileOutputStream(file.getAbsoluteFile());
            workbook.write(stream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try{
                //这里调用了IO工具包控制开关
                closeQuietly(workbook);
                closeQuietly(stream);
            }catch (Exception e){
                closeQuietly(stream);
            }

        }
        return file;
    }
}
